//
//  SqliteManager.swift
//  FMDBDemo
//
//  Created by Apple on 16/9/11.
//  Copyright © 2016年 KY. All rights reserved.
//

import UIKit

private let DBName = "SinaWeibo.db"
private let DBPath = (NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).last! as NSString).appendingPathComponent(DBName)

//  数据库操作专用类
class SqliteManager: NSObject {
    
    
    
    //  单例全局访问点
    static let sharedManager: SqliteManager = SqliteManager()
    
    //  懒加载数据库操作队列
    lazy var queue: FMDatabaseQueue = FMDatabaseQueue(path: DBPath)
    
    //  构造函数私有化
    private override init() {
        super.init()
    
        print(DBPath)
        
        createTable()
    
    }
    
    //  创建表
    private func createTable() {
    
        let path = Bundle.main.path(forResource: "db.sql", ofType: nil)!
        //  创建sql语句
        let sql = try! String(contentsOfFile: path)
        
        //  执行sql语句
        queue.inDatabase { (db) in
            let result = db!.executeStatements(sql)
            if result {
                print("创表成功")
            } else {
                print("创表失败")
            }
        }
        
    }
    
    //  插入数据
    func insert() {
        //  创建sql
        let sql = "insert into t_person(name, age) values(?,?)"
        //  执行sql
        queue.inDatabase { (db) in
            let result = db!.executeUpdate(sql, withArgumentsIn: ["张三", 20])
            if result {
                print("插入成功")
            } else {
                print("插入失败")
            }
        }
    }
    
    //  查询数据
    func query() -> Void {
        //  创建sql
        let sql = "select * from t_person"
        //  执行sql
        queue.inDatabase { (db) in
            //  结果集
            let result = db!.executeQuery(sql, withArgumentsIn: nil)!
            //  遍历结果集
//            while result.next() {
//                let name = result.object(forColumnName: "name")
//                let age = result.object(forColumnName: "age")
//                let id = result.object(forColumnName: "id")
//                
//                print("\(name), \(age), \(id)")
//            }
            
            while result.next() {
                let name = result.object(forColumnIndex: 0)
                let age = result.object(forColumnIndex: 1)
                let id = result.object(forColumnIndex: 2)
                
                print("\(name), \(age), \(id)")
            }
            
        }
    }
    
    //  修改数据
    func update() -> Void {
        //  创建sql 
        let sql = "update t_person set name = ?, age = ? where id = ?"
        //  执行sql
        queue.inDatabase { (db) in
            let result = db!.executeUpdate(sql, withArgumentsIn: ["李三", 30, 2])
            if result {
                print("修改成功")
            } else {
                print("修改失败")
            }
        }
        
        
    }
    
    //  删除数据
    func delete() -> Void {
        //  创建sql
        let sql = "delete from t_person where id = 1"
        //  执行sql
        queue.inDatabase { (db) in
            let result = db!.executeUpdate(sql, withArgumentsIn: nil)
            if result {
                print("删除成功")
            } else {
                print("删除失败")
            }
        }
        
    }
    
    //  查询输出字典的方式
    func queryToDic() -> Void {
    
        //  创建sql
        let sql = "select * from t_person"
        //  执行sql
        queue.inDatabase { (db) in
            let resultSet = db!.executeQuery(sql, withArgumentsIn: nil)!
            while resultSet.next() {
             
                //  获取列数
                let colsCount = resultSet.columnCount()
                //  空的可变字典存储遍历的键值对
                var dic = [String: Any]()
                //  遍历列数
                for i in 0..<colsCount {
                    let key = resultSet.columnName(for: i)!
                    let value = resultSet.object(forColumnIndex: i)!
                    dic.updateValue(value, forKey: key)
                }
                
                //  遍历输出字典
                print("----------------")
                print(dic)
                
            }
            
            
            
        }
        
    }
    
    
    //  通用的方法，根据传入的查询sql语句返回字典数组
    func querySqlToDic(sql: String) -> [[String: Any]] {
        
        var tempArray = [[String: Any]]()
        //  执行sql
        queue.inDatabase { (db) in
            let resultSet = db!.executeQuery(sql, withArgumentsIn: nil)!
            let colsCount = resultSet.columnCount()
            while resultSet.next() {
                
                //  定义空的可变字典
                var dic = [String: Any]()
                //  遍历列数，获取键值对
                for i in 0..<colsCount {
                    let key = resultSet.columnName(for: i)!
                    let value = resultSet.object(forColumnIndex: i)!
                    dic.updateValue(value, forKey: key)
                }
                tempArray.append(dic)
                
            }
        }
        return tempArray
    }
    
    
    
    
    
    
    
}
